# backend/crud.py
from typing import List, Tuple
from sqlalchemy import select, or_, func
from sqlalchemy.ext.asyncio import AsyncSession
from models import GB

async def search_drugs(
    db: AsyncSession,
    keyword: str,
    offset: int,
    limit: int
) -> Tuple[List[GB], int]:
    stmt = select(GB).where(
        or_(
            GB.a2.like(f"%{keyword}%"),
            GB.ybm.like(f"%{keyword}%"),
            GB.a29.like(f"%{keyword}%"),
        )
    ).order_by(GB.a2).offset(offset).limit(limit)

    total_stmt = select(func.count()).select_from(
        select(GB).where(
            or_(
                GB.a2.like(f"%{keyword}%"),
                GB.ybm.like(f"%{keyword}%"),
                GB.a29.like(f"%{keyword}%"),
            )
        ).subquery()
    )

    total = (await db.execute(total_stmt)).scalar_one()
    result = (await db.execute(stmt)).scalars().all()
    return result, total

async def get_drug_by_id(db: AsyncSession, drug_id: int) -> GB | None:
    stmt = select(GB).where(GB.a25 == drug_id)
    res = await db.execute(stmt)
    return res.scalar_one_or_none()